﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using Ledict.Core.Data;
using System.Data.Common;
using Ledict.ConditionExtract.Model;
using Ledict.ConditionExtract.Common;
using LdtJudge.DatabaseHandle;

namespace Ledict.ConditionExtract.DAL
{
    /// <summary>
    /// 法定刑幅度档次表
    /// </summary>
    public class XF_RelatStatus_GroupDAL
    {
        private string JudgeDoc1;
        public XF_RelatStatus_GroupDAL()
        {
            JudgeDoc1 = "JudgeDoc";
        }

        /// <summary>
        /// 增加一条数据
        /// </summary>
        public bool Add(XF_RelatStatus_GroupModel model)
        {         
            string sql=string.Format(@"insert into XF_RelatStatus_Group(
			RG_ID,PR_groupID,CC_ID)
			values ('{0}','{1}','{2}')", model.RG_ID,model.PR_groupID, model.CC_ID);
            Database oBase = DatabaseFactory.CreateDatabase(JudgeDoc1);
            DbCommand oCmd = oBase.GetSqlStringCommand(sql);
            if (oBase.ExecuteNonQuery(oCmd) > 0)
            {
                return true;
            }
            return false;
        }

        /// <summary>
        /// 删除一条数据
        /// </summary>
        public bool Delete(string rgId)
        {
            string sql = string.Format(@" delete from XF_RelatStatus_Group where PR_groupID='{0}' ", rgId);           
            Database oBase = DatabaseFactory.CreateDatabase(JudgeDoc1);
            DbCommand oCmd = oBase.GetSqlStringCommand(sql);
            if (oBase.ExecuteNonQuery(oCmd) > 0)
            {
                return true;
            }
            return false;
        }

        /// <summary>
        /// 获取情形数据
        /// </summary>
        /// <param name="ccid">节点信息</param>
        /// <returns></returns>
        public DataTable GetQxData(string ccid)
        {
            string sql = string.Format(@"     select CC_Name from XF_RelatStatus_Group a
  join SYS_CaseCause b on a.CC_ID=b.CC_ID
  where  PR_groupID ='{0}'  ", ccid);
            Database oBase = DatabaseFactory.CreateDatabase(JudgeDoc1);
            DbCommand oCmd = oBase.GetSqlStringCommand(sql.ToString());
            var otB = oBase.ExecuteDataSet(oCmd).Tables[0];
            if (otB.Rows.Count > 0)
            {
                return otB;
            }
            return new DataTable();
        }

        /// <summary>
        /// 修改字典值名称，通过PG_ID
        /// </summary>
        public bool UpdateName(XF_Punishment_GradeModel model)
        {
            string sql = string.Format(@" update XF_Punishment_Grade set PG_CaseCauseID='{0}',PG_Grade='{1}' where PG_ID='{2}' ", model.PG_CaseCauseID,model.PG_Grade,model.PG_ID);            
            Database oBase = DatabaseFactory.CreateDatabase(JudgeDoc1);
            DbCommand oCmd = oBase.GetSqlStringCommand(sql);
            if (oBase.ExecuteNonQuery(oCmd) > 0)
            {
                return true;
            }
            return false;
        }


        /// <summary>
        /// 获取节点是否被引用组合情形
        /// </summary>
        public DataTable GetGroupCount(string ccId)
        {
            //string sql = string.Format(@" select CC_Name from  sql67.[Ldt_JudgeDocSystem].[dbo].[View_SYS_CaseCause] where CC_ID in( select distinct CC_ID from XF_RelatStatus_Group where CC_ID in(select CC_ID from sql67.[Ldt_JudgeDocSystem].[dbo].[View_SYS_CaseCause] where charindex(',{0},', ',' + AllParentId + ',')>0)) ", ccId);
            string sql = string.Format(@" select CC_Name from  SYS_CaseCause where CC_ID in( select distinct CC_ID from XF_RelatStatus_Group where CC_ID in(select CC_ID from SYS_CaseCause where charindex(',{0},', ',' + AllParentId + ',')>0)) ", ccId);
            Database oBase = DatabaseFactory.CreateDatabase(JudgeDoc1);
            DbCommand oCmd = oBase.GetSqlStringCommand(sql);
            oCmd.CommandTimeout = 5000;
            var otB = oBase.ExecuteDataSet(oCmd).Tables[0];
            if (otB.Rows.Count > 0)
            {
                return otB;
            }
            return new DataTable();
        }

        /// <summary>
        /// 判断单一情形是否已经存在
        /// </summary>
        /// <param name="ccid"></param>
        /// <param name="pgGrade"></param>
        /// <param name="situationTypeId"></param>
        /// <returns></returns>
        public string GetQxOldData(string ccid, string pgGrade, string situationTypeId)
        {
            string[] textArray1 = new string[] { "     SELECT count(1) from XF_PunishimentGrade_RelatStatus a join XF_RelatStatus_Group b on a.PR_groupID=b.PR_groupID\r\njoin XF_Punishiment_RelatStatus c on c.PR_groupID=b.PR_groupID\r\nwhere a.PG_Grade='", pgGrade, "' and b.CC_ID='", ccid, "' and c.PR_SituationTypeID='", situationTypeId, "'  " };
            string str = string.Concat(textArray1);
            Database database1 = DatabaseFactory.CreateDatabase(this.JudgeDoc1);
            return database1.ExecuteScalar(database1.GetSqlStringCommand(str.ToString())).ToString();
        }






    }
}
